<?php
class ModelProduct
{
	public function construct()
	{
		
	}
	
	public function listProducts($limit,$catid=null,$subcatid=null,$searchkey=null)
	{
		$sql = "SELECT *
				FROM tbl_products  where 1=1 " ;
                if(isset($catid) && strlen($catid)>0){
                     $sql .= " and product_cat_id=".$catid." ";
                }

                if(isset($subcatid) && strlen($subcatid)>0){
                     $sql .= " and product_subcat_id=".$subcatid." ";
                }

                if(isset($searchkey) && strlen($searchkey)>0){

                     $sql .= " and searchstr like '%".$searchkey."%' ";
                }
                $sql .=" order by productid desc  ";
                $sql .= $limit;
                //echo $sql;
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	
	public function updateProduct($formVal)
	{
               // print_r($formVal);
		$productid = $formVal['productid'];
		$product_title = $formVal['product_title'];
		$product_desc_en = $formVal['product_desc_en'];
		$product_desc_detail = $formVal['product_desc_detail'];
		$item_code = $formVal['item_code'];
		$product_price_lkr = $formVal['product_price_lkr'];
		$product_qty = $formVal['product_qty'];
		$category = $formVal['category'];
        
		if(empty($formVal['discount']))
		{
			$discount = "NULL";
		} else {
			$discount = $formVal['discount'];
		}
		
		$subcategory = $formVal['subcat'];
        if(empty($subcategory)){
          $subcategory=0;
        }
		$newitem = $formVal['newitem'];
		$status = $formVal['status'];
		
		$sql = "UPDATE tbl_products SET product_title = '" . $product_title . "', item_code = '" . $item_code . "',
product_price_lkr=".$product_price_lkr.",quantity=".$product_qty.", product_dsc_en = '" . $product_desc_en . "', product_dsc_detail = '" . $product_desc_detail . "',  created_date = '" . date('Y-m-d') . "',  status = " . $status . "
	,product_cat_id =".$category." ,product_subcat_id=".$subcategory.",discount=".$discount." WHERE productid = " . $productid . " ";
		$product_arr = DB::exec($sql);
		
		if($product_arr)
		{
			echo "<span class='success'>Product details has been successfully updated</span>";
		}
	}
	public function saveProduct($formVal)
	{
           // print_r($formVal);
                $item_code = $formVal['item_code'];
		$product_title = $formVal['product_title'];
		$productdesc_en = $formVal['product_desc_en'];
                $productdesc_detail=$formVal['product_desc_detail'];
		$product_price_lkr = $formVal['product_price_lkr'];
		$product_qty = $formVal['product_qty'];
		$category = $formVal['category'];
		$subcategory = !empty($formVal['subcat'])?$formVal['subcat']:0;
		$status = $formVal['status'];
                if(empty($formVal['discount']))
		{
			$discount = "NULL";
		} else {
			$discount = $formVal['discount'];
		}

		$sql = "insert into tbl_products (item_code,product_title,product_dsc_en,product_dsc_detail
                    ,product_price_lkr,quantity,product_cat_id,product_subcat_id,created_date,status,discount) values
                     ('".$item_code."','".$product_title."','".$productdesc_en."','".$productdesc_detail."',".$product_price_lkr.",".$product_qty.",".$category.",".$subcategory.",now(),".$status.",".$discount.")";

		//echo $sql;

		$product_arr = DB::exec($sql);

		if($product_arr)
		{
			echo "<span class='success'>Product details has been successfully updated</span>";
		}
	}

	public function getProductByItemCode($productid)
	{
		$sql = "SELECT P.discount,P.productid,P.item_code,P.product_title, P.product_dsc_en, P.product_dsc_detail, P.product_dsc_tamil, P.product_price_lkr, P.quantity, P.product_cat_id, P.product_subcat_id, P.created_date, P.status, C.cat_desc
				FROM tbl_products P
				LEFT JOIN tbl_category C ON C.cat_id = P.product_subcat_id
				WHERE P.productid =".$productid." ";
		//echo $sql;
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}


	public function getFeaturesbyProductId($productid)
	{
           
		$sql = "SELECT * from  tbl_productfeatures  where productid =".$productid." ";
		//echo $sql;
		$item_arr = DB::fetchAll($sql);

		return $item_arr;
	}

        public function saveFeaturesbyProductId($request)
	{
            //print_r($request);
            $dberror=0;
             $insertstructarray=array();
             foreach($request["structkey"] as $key=>$val)
             {
                 if(strlen($request["structkeyopt"][$key])>0){
                    $insertstructarray[$request["structkeyopt"][$key]]=$request["structval"][$key];
                 }
                 else{
                      $insertstructarray[$val]=$request["structval"][$key];
                 }
                
             }
             //print_r($insertstructarray);
                DB::exec("SET AUTOCOMMIT=0");
                DB::exec("START TRANSACTION");
		        $sqldelete =  DB::exec("delete from  tbl_productfeatures  where productid =".$request["productid"]."");
                foreach($insertstructarray as $structurekey=>$structurevalue){
                    if(strlen($structurekey)>0){
                        $sqlinsert=  DB::exec("insert  into  tbl_productfeatures (structkey,structvalue,productid) values ('".$structurekey."','".$structurevalue."',".$request["productid"].")");
                        if(mysql_error())
                        {
                            $dberror=1;
                        }
                        $keyexistintemptablesql="select * from tbl_tempstructuretable where structkey='".$structurekey."' and catid=".$request["catid"]."";
                        $keyexistintemptable=DB::fetchAll($keyexistintemptablesql);
                        if(!$keyexistintemptable)
                        {
                            $inserttemptablesql="insert into  tbl_tempstructuretable (catid,structkey) values (".$request["catid"].",'".$structurekey."')";
                            $inserttemptablesql;
                            $inserttemptable=DB::exec($inserttemptablesql);
                            if(mysql_error())
                                       {
                                            $dberror=1;
                                       }
                        }
                    }
                }
                if($dberror==0)
                {
                     DB::exec("COMMIT");
                     echo "<span class='success'>Product details has been successfully updated</span>";
                }
                else
                {
                    DB::exec("ROLLBACK");
                    echo "<span class='error'>Update failed.Database error occured!</span>";
                }

              
               // exit;
		return $success;
	}

        function getcatidfromproductid($productid)
        {
            $sql="select product_cat_id as catid from tbl_products where productid=".$productid."";
            $catid = DB::fetchRow($sql);
            return $catid["catid"];
        }

        function listStructfields($catid)
        {
            $sql="select *   from tbl_tempstructuretable where catid=".$catid."";
            $structarr = DB::fetchAll($sql);
            return $structarr;
        }

        function deleteFeaturesbyProductId($formVal)
        {
             $productid = $formVal['productid'];
	     $structid = $formVal['structid'];
             $sql="delete from tbl_productfeatures where structid=".$structid." and productid=".$productid."";
            // echo $sql;
             $delete=  DB::exec($sql);
             if($delete)
             {
                echo "<span class='success'>Fetaure delete successfully!</span>";
             }
           

        }
	
	public function getProductImages($productid)
	{
		DB::exec("SET NAMES 'utf8'");
		
		$sql = "SELECT *
				FROM tbl_productimage
				WHERE productid = " . $productid . " ";				
		//echo $sql;
		$item_arr = DB::fetchAll($sql);
		
		return $item_arr;
	}
	
	public function addProductImage($productid, $filename)
	{
		$sql1 = "SELECT COUNT(image_id) AS IMGCOUNT
				 FROM tbl_productimage
				 WHERE filename = '" . $filename . "' ";
		
		$rs = DB::fetchRow($sql1);
		$rw_count = $rs['IMGCOUNT'];
		if($rw_count == 0)
		{
			$sql2 = "INSERT INTO tbl_productimage(productid, filename)
					VALUES('" . $productid . "', '" . $filename . "')";
			$add_productimage = DB::exec($sql2);
			if($add_productimage)
			{
				echo "success";
			}
		} else {
			echo "error";
		}
	}
	
	public function delProductImage($productid,$image_id)
	{
		$sql1 = "SELECT filename
				 FROM tbl_productimage
				 WHERE productid = " . $productid ." and image_id=".$image_id."";
		
		$rs = DB::fetchRow($sql1);
		
		$del_thumb = unlink("../images/products/thumbs/" . $rs['filename']);
		$del_large = unlink("../images/products/" . $rs['filename']);
		if($del_large)
		{
			$sql2 = "DELETE FROM tbl_productimage
					 WHERE productid = " . $productid ." and image_id=".$image_id."";
			//echo $sql2;
			$del_img = DB::exec($sql2);
			if($del_img)
			{
				echo "Selected product images has been successfully deleted";
			}
		}
	}
        
        public function gettotalrowcount($catid=null,$subcatid=null){
            
            $sql="select count(*) as count  from tbl_products where 1=1  ";
            if(isset($catid)){
                $sql.= " and  product_cat_id=".$catid."  ";
            }
            if(isset($subcatid)){

                $sql.= " and product_cat_id=".$catid." and product_subcat_id=".$subcatid."   ";
            }

           // echo $sql;
            $count = DB::fetchRow($sql);
            return $count["count"];
        }

        public function checkitemcodeupdate($request){

                $itemcode=$request["itemcode"];
                $productid=$request["productid"];
		$sql = "SELECT *
				FROM tbl_products
				WHERE item_code = '" . $itemcode . "' and productid<>'".$productid."' ";
		//echo $sql;
		$item_arr = DB::fetchAll($sql);

		if($item_arr){
                    echo 1;
                }
                else{
                    echo 2 ;
                    
                }
            
        }
           public function checkitemcode($request){

                $itemcode=$request["itemcode"];
		$sql = "SELECT *
				FROM tbl_products
				WHERE item_code = '" . $itemcode . "' ";
		//echo $sql;
		$item_arr = DB::fetchAll($sql);

		if($item_arr){
                    echo 1;
                }
                else{
                    echo 2 ;

                }

        }
        public function deleteproduct($formVal){
          
             $productid = $formVal['productid'];
             $sql="delete from tbl_products where  productid=".$productid."";
            // echo $sql;
             $delete=  DB::exec($sql);
             if($delete)
             {
                echo "<span class='success'>Product deleted successfully!</span>";
             }

             
        }
          public function deletehomepageproduct($formVal){

             $productid = $formVal['productid'];
             $sql="delete from tbl_homepageproducts where  productid=".$productid."";
             echo $sql;
             $delete=  DB::exec($sql);
             if($delete)
             {
                echo "<span class='success'>Product deleted successfully!</span>";
             }


        }
        function createsearchstr()
        {

             $sql1="select * from  tbl_products ";

            $prodtablearr= DB::fetchAll($sql1);
           // print_r($prodtablearr);
            foreach($prodtablearr as $arr){
                 $structkey="";
                $sql2="select * from   tbl_productfeatures where productid=".$arr["productid"]." ";
                $structvalarr=DB::fetchAll($sql2);
                 foreach($structvalarr as $structarr){
                    $structkey.= " ".$structarr["structvalue"];
                 }
               //  echo $structkey;
               $sql3="update tbl_products p set searchstr=concat(CAST(p.productid AS CHAR),' ',p.item_code,' ',p.product_title,' ',ifnull(p.product_dsc_en,' '),' ',ifnull((select cat_desc from tbl_category c where c.cat_id=p.product_cat_id ),' '),' ',ifnull((select cat_desc from tbl_category c where c.cat_id=p.product_subcat_id ),' '),' ','".$structkey."') where p.productid=".$arr["productid"]."";
               $res= DB::exec($sql3); 


        }
        $res2=DB::exec(" call sp_printerfinder ");

       // $this->sendoutofstockmail();
       // $res3= DB::exec(" call sp_deleteinvalidstructkeys ");

        if($res && $res2 /*&& $re3*/){
             echo "<span class='success'>Search strings updated successfully!</span>";
        }
        else{
            echo "<span class='error'>Update failed! Datbase error occured!</span>";
        }
	}
	
	public function listhomepageproducts()
	{
		$sql = "SELECT h.sortorder,h.productid,h.itemcode,p.product_title,IFNULL(i.filename,'NoImage.jpg') as filename   FROM tbl_homepageproducts h
                    inner join tbl_products p on h.productid=p.productid
                    left join tbl_productimage i on i.productid=p.productid order by h.sortorder

                         ";
		$itemcode_arr = DB::fetchAll($sql);
		//print_r($itemcode_arr);
		return $itemcode_arr;
	}

        public function sorthomepage($productOrder)
	{
             // print_r($productOrder);
		foreach($productOrder["product"] as $key=>$value)
                {
                    $sql=" update tbl_homepageproducts set  sortorder=".$key." where productid=".$value."";
                    $update= DB::exec($sql);
                }
		
		return $update;
	}

         public function checkitemcodehomepage($itemcode){

		$sql = "SELECT *
				FROM tbl_products
				WHERE item_code = '" . $itemcode . "' and status=1 and quantity>0 ";
		//echo $sql;
		$item_arr = DB::fetchAll($sql);

		if($item_arr){
                    return true;
                }
                else{
                     return false;

                }

        }
        
         public function addhomepageproduct($request)
	{ 
             $itemcode=$request["itemcode"];
             $sql=" call sp_addhomepageproduct('".$itemcode."')";
            // $update=DB::exec($sql);
             if(!$this->checkitemcodehomepage($itemcode))
             {
                 echo "F";
             }
	     else
             {
                 $update=DB::exec($sql);
                 if($update){
                     echo "<span class='success'>Home Page products updated successfully!</span>";
                 }
                 
             }
             
	}

      

        
}

?>